﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ZX.CRM.Library;
using ZX.BLL.Extend;
using ZX.DBModule;
using System.IO;
using ZX.Common;
using System.Data;
using ZX.DAL.DBExtend;
namespace ZX.CRM.Bussiness
{
    public partial class BTCrmTeachHodometer : BLL.Extend.BaseBussnies
    {

        /// <summary> 
        /// 获取最新资料
        /// </summary>
        /// <returns></returns>
        public IList<V_CrmTeachHodometer> getAttachList(int tn)
        {
            this.entity = new V_CrmTeachHodometer();
            return this.GetEntitysBySqlComm<V_CrmTeachHodometer>("select tc.CourseContent,ta.TrAttachID,ta.Explain, tc.CourseName as CourseName,ta.AttachName as AttachName,ta.Address as Address from TrAttach ta,TrCourse tc where ta.ParentID=tc.TrCourseID and ta.IsFirstPageView='Y'");
        }

        /// <summary>
        /// 获取讲师信息
        /// </summary>
        /// <returns></returns>
        public IList<V_CrmCustomer> getV_CrmCustomer(string userid, string str)
        {
            this.entity = new V_CrmCustomer();

            if (str == "")
            {
                if (userid == "ADMIN")
                {
                    return this.GetEntitysBySqlComm<V_CrmCustomer>("select top 10 * from V_CrmCustomer");
                }
                else
                {
                    return this.GetEntitysBySqlComm<V_CrmCustomer>("select top 10 * from V_CrmCustomer where AreaId in (select value from fn_Split ((select DeptIds from V_UserEmp where userid='" + userid + "'),','))");
                }
            }
            else
            {
                if (userid == "ADMIN")
                    return this.GetEntitysBySqlComm<V_CrmCustomer>("select top 10 * from V_CrmCustomer where CustomerName like '%" + str + "%'");
                else
                    return this.GetEntitysBySqlComm<V_CrmCustomer>("select top 10 * from V_CrmCustomer where AreaId in (select value from fn_Split ((select DeptIds from V_UserEmp where userid='" + userid + "'),',')) and CustomerName like '%" + str + "%'");

            }
            
        }

        public int deleteCrmInnerTeachContent(string PID)
        {
            string sql = "delete from [CrmInnerTeachContent] where [InnerSumID]='" + PID + "'";
            DbSql db = new DbSql();
            return db.ExecuteSql(sql);
        }

        public bool IsExistCus(string cusid)
        {
            string sql = "SELECT COUNT(*) nums FROM  [CrmTeachHodometer] where CusID='" + cusid + "'";
            DbSql db = new DbSql();
            int i= db.ExecuteScalar(sql);
            if (i > 0)
                return true;
            else
                return false;
        }

        public bool IsExistTeachTime(string HmDate, string TeachID, string HmType)
        {
            string sql = "SELECT COUNT(*) nums FROM [CrmTeachHodometer] where HmDate='" + HmDate + "' and TeachID='" + TeachID + "' and HmType='" + HmType + "'";
            DbSql db = new DbSql();
            int i = db.ExecuteScalar(sql);
            if (i > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 根据讲师ID，年月获取讲师日行程
        /// </summary>
        /// <param name="teachid"></param>
        /// <param name="ym"></param>
        /// <returns></returns>
        public string GetTeachCal(string teachid, string ym)
        {
            string sql = "SELECT substring([HmDate],7,2)+replace([DType],',','') HDT FROM  [V_TEACHCALENDARMOBI] where [TeachID]='" + teachid + "' and [HmDate] like '%" + ym + "%'";
            
            DataTable dt = BackDTbySQL(sql);
            string back = "";
            if (dt.Rows.Count > 0)
            {
                for(int i=0;i<dt.Rows.Count;i++)
                {
                    back+=dt.Rows[i][0].ToString()+";";
                }
                back = back.Substring(0,(back.Length-1));
            }
            return back;
        }

        //获取讲师行程信息
        public DataTable GetTeachContent(string teachid, string time)
        {
            string sql = "SELECT  CONVERT(varchar(12), [HmDate], 112) HmDate"
            + ",[HmType]"
            + ",[CusID]"
            + ",[TeachID]"
            + ",[CustomerName]"
            + ",[Decisionmaker]"
            + ",[DecisionPosition]"
            + ",[Area]"
            + ",[teachname]"
            + ",[addusername]"
            + ",[EXP1] courseaddress"
            + ",[EXP2] coursetime"
            + " FROM [V_CrmTeachHodometer] where CONVERT(varchar(12), [HmDate], 112)='"+time+"' and TeachID='"+teachid+"'";

            return BackDTbySQL(sql);
        }


        //获取部门
        public DataTable getDept()
        {
            string sql = "SELECT [DeptID] ,[DeptName] FROM [DatDepartment] where ParentID='FB282647-FFE0-440B-81B2-1466D3EAC417'";
            return BackDTbySQL(sql);
        }

        private DataTable BackDTbySQL(string sql)
        {
            DbSql db = new DbSql();
            return db.ExecuteQuery(sql);
        }

        public DataTable GetThodometerId(string para)
        {
            string[] pas = para.Split('︾');
            string date = pas[0].Substring(0, 8);
            string type = pas[0].Substring(8, 2);
            string sql = "SELECT [ThodometerId],[CusID] FROM [dbo].[V_CrmTeachHodometer] where CONVERT(varchar(12), [HmDate], 112)='"+date+"' and TeachID='" + pas[1] + "' and HmType='" + type + "'";

            return BackDTbySQL(sql);
        }

        public DataTable GetCusInfoByid(string id)
        {
            string sql = @"SELECT TOP 1 [ID]
      ,[CustomUser]
      ,[ShowUser]
      ,[Code]
      ,[State]
      ,[Province]
      ,[City]
      ,[Area]
      ,[Address]
      ,[CustomerName]
      ,[MnemonicCode]
      ,[IsHQ]
      ,[LevelId]
      ,[OriginId]
      ,[TypeId]
      ,[AreaId]
      ,[TradeId]
      ,[ProductId]
      ,[PersonId]
      ,[UnionId]
      ,[PropertyId]
      ,[DecisionPosition]
      ,[Decisionmaker]
      ,[DecisionTel]
      ,[DecisionEmail]
      ,[Tel2]
      ,[Post]
      ,[Fax]
      ,[Email]
      ,[CompanyNetting]
      ,[CompanyCreateDate]
      ,[CompanyProduct]
      ,[CompanyAttention]
      ,[CompanyGain]
      ,[PersonSum]
      ,[PersonSum2]
      ,[PersonSum3]
      ,[ProductSum]
      ,[ProductSum2]
      ,[ProductSum3]
      ,[CusTestimony]
      ,[RADTargetId]
      ,[CusTrainbudget]
      ,[ManagerQuestion]
      ,[StrongPoint]
      ,[IsPrevate]
      ,[AttachList]
      ,[IsValue]
      ,[Explain]
      ,[SaleadviserId]
      ,[SaleaddTime]
      ,[AddUser]
      ,[AddTime]
      ,[TrainHistory]
      ,[Painful]
      ,[Pullulation]
      ,[LectorUser]
      ,[Lectortime]
      ,[CustomeerMgr]
      ,[Mgrtime]
      ,[exp1]
      ,[exp2]
      ,[exp3]
      ,[exp4]
      ,[exp5]
      ,[exp6]
      ,[exp7]
      ,[exp8]
      ,[exp9]
      ,[exp10]
      ,[LevelName]
      ,[OriginName]
      ,[TypeName]
      ,[TradeName]
      ,[DeptName]
      ,[ProductName]
      ,[PersonName]
      ,[SaleadviserName]
      ,[AddUserName]
      ,[LectorName]
      ,[CustomerMgrName]
      ,[DecisionPersonsun]
      ,[HighPersonsum]
      ,[MiddlePersonsum]
      ,[BelowPersonsum]
      ,[TrainCompany]
      ,[TrainDate]
      ,[AffectDecisioner]
      ,[CustomerPhylogeny]
      ,[Homeplace]
      ,[BirthdayType]
      ,[DecisionBirthday]
      ,[SchoolScientifc]
      ,[DecisionFancy]
      ,[Credit]
      ,[Character]
      ,[Tel1]
      ,[DecisionSex]
      ,[DecisionTick]
      ,[DecisionRest]
      ,[DecisionLearn]
      ,[DecisionFeelType]
      ,[DecisionType]
      ,[DecisionBuyType]
      ,[DecisionAttitude]
      ,[PayOffNum1]
      ,[PayOffNum2]
      ,[PayOffNum3]
      ,[CustomerSaleTyte]
      ,[IsTrainDept]
      ,[CompeterCustomer]
      ,[TradeDegree]
      ,[CustomerDream]
      ,[CustomerPromis]
      ,[ProfitNum1]
      ,[ProfitNum2]
      ,[ProfitNum3]
      ,[DecisionTypName]
      ,[DecisionBuyTypeName]
      ,[DecisionFeelTypeName]
      ,[CustomerSaleTypeName]
      ,[DetailAddress]
  FROM [V_CrmCustomerInfo] where id='" + id + "'";
            return BackDTbySQL(sql);
        }

        //获取客户信息
        public DataTable GetCustomerByid(string id)
        {
            string sql = @"select [ID]
      ,[Code]
      ,[CustomerName]
      ,[Decisionmaker]
      ,[DecisionPosition]
      ,[DecisionTel]
      ,[TypeId]
      ,[LevelId]
      ,[OriginId]
      ,[AreaId]
      ,[TradeId]
      ,[CustomerType]
      ,[CustomerLevel]
      ,[CustomerOrigin]
      ,[CustomerTrade]
      ,[CustomerArea]
      ,[IsPrevate]
      ,[ProductId]
      ,[PhraseID]
      ,[ProductCount]
      ,[UnionId]
      ,[PersonId]
      ,[PersonCount]
      ,[AddUser]
      ,CONVERT(varchar(10),[AddTime], 120) AddTime
      ,[UName]
      ,[ProductSum]
      ,[ProductSum2]
      ,[ProductSum3]
      ,[IsValue]
      ,[UserID]
      ,[SaleadName]
      ,CONVERT(varchar(10),[CompanyCreateDate], 120) CompanyCreateDate
      ,[State]
      ,[Province]
      ,[City]
      ,[Area]
      ,[Address]
      ,[PropertyId]
      ,[Tel1]
      ,[DecisionEmail]
      ,[Tel2]
      ,[Post]
      ,[Fax]
      ,[Email]
      ,[CompanyNetting]
      ,[CompanyProduct]
      ,[PersonSum]
      ,[PersonSum2]
      ,[PersonSum3]
      ,[CompanyGain]
      ,[CompanyAttention]
      ,[Explain]
      ,[SaleadviserId]
      ,CONVERT(varchar(10),[SaleaddTime], 120) SaleaddTime
      ,[exp1]
      ,[exp2]
      ,[exp3]
      ,[exp4]
      ,[exp5]
      ,[exp6]
      ,[exp7]
      ,[exp8]
      ,[exp9]
      ,[exp10]
      ,[AttachList]
      ,[StrongPoint]
      ,[ManagerQuestion]
      ,[CusTrainbudget]
      ,[RADTargetId]
      ,[CusTestimony]
      ,[IsHQ]
      ,[MnemonicCode]
      ,[EmpID]
      ,[DeptID]
      ,[DeptName]
      ,[Homeplace]
      ,[BirthdayType]
      ,CONVERT(varchar(10),[DecisionBirthday], 120) DecisionBirthday
      ,[SchoolScientifc]
      ,[DecisionFancy]
      ,[Credit]
      ,[Character]
      ,[DecisionPersonsun]
      ,[HighPersonsum]
      ,[MiddlePersonsum]
      ,[BelowPersonsum]
      ,[TrainCompany]
      ,CONVERT(varchar(10),[TrainDate], 120) TrainDate
      ,[AffectDecisioner]
      ,[CustomerPhylogeny]
      ,[Pullulation]
      ,[Painful]
      ,[TrainHistory]
      ,[DecisionSex]
      ,[DecisionTick]
      ,[DecisionRest]
      ,[DecisionLearn]
      ,[DecisionFeelType]
      ,[DecisionType]
      ,[DecisionBuyType]
      ,[DecisionAttitude]
      ,[PayOffNum1]
      ,[PayOffNum2]
      ,[PayOffNum3]
      ,[CustomerSaleTyte]
      ,[IsTrainDept]
      ,[CompeterCustomer]
      ,[TradeDegree]
      ,[CustomerDream]
      ,[CustomerPromis]
      ,[LectorUser]
      ,[CustomeerMgr]
      , CONVERT(varchar(10),[Lectortime], 120) Lectortime
      ,CONVERT(varchar(10),[Mgrtime], 120) Mgrtime
      ,[DetailAddress]
      ,[ProfitNum1]
      ,[ProfitNum2]
      ,[ProfitNum3] FROM  [V_CrmCustomer] where id='" + id+"'";
            return BackDTbySQL(sql);
        }
        //获取行程总结信息
        public DataTable GetCrmTeachSumupByid(string id)
        {
            string sql = @"select [ID]
      ,[Code]
      ,[OrderId]
      ,[ThodometerId]
      ,CONVERT(varchar(10), [PrelectDate], 120) PrelectDate
      ,[IsBargain]
      ,[IsStandard]
      ,[IsValue]
      ,[JWXID]
      ,[IsGripe]
      ,[GripeExplain]
      ,[IsClass]
      ,[ClassExplain]
      ,[TeachPlace]
      ,[CustomerPromis]
      ,[TutorId]
      ,[DeptId]
      ,[Explain]
      ,[Adduser]
      ,CONVERT(varchar(10), [Addtime], 120) Addtime
      ,[EXP1]
      ,[EXP2]
      ,[EXP3]
      ,[EXP4]
      ,[DeptName]
      ,[UName]
      ,CONVERT(varchar(10), [HmDate], 120) HmDate
      ,[Expr2]
      ,[HmType]
      ,[OrderCode]
      ,[CustomerCode]
      ,[State]
      ,[Province]
      ,[City]
      ,[Area]
      ,[Address]
      ,[CustomerName]
      ,[MnemonicCode]
      ,[IsHQ]
      ,[Decisionmaker]
      ,[DecisionPosition]
      ,[DecisionTel]
      ,[DecisionEmail]
      ,CONVERT(varchar(10), [CompanyCreateDate], 120) CompanyCreateDate
      ,[CompanyProduct]
      ,[CompanyAttention]
      ,[CompanyGain]
      ,[ProductSum]
      ,[CusTestimony]
      ,[CusTrainbudget]
      ,[ManagerQuestion]
      ,[StrongPoint]
      ,[Expr1]
      ,[Character]
      ,[DecisionFancy]
      ,CONVERT(varchar(10), [DecisionBirthday], 120) DecisionBirthday
      ,[Credit]
      ,[SchoolScientifc]
      ,[BirthdayType]
      ,[Homeplace]
      ,[CustomerPhylogeny]
      ,[AffectDecisioner]
      ,CONVERT(varchar(10),[TrainDate], 120) TrainDate
      ,[TrainCompany]
      ,[BelowPersonsum]
      ,[MiddlePersonsum]
      ,[HighPersonsum]
      ,[DecisionPersonsun]
      ,[CustomerMgrName]
      ,[LectorName]
      ,[AddUserName]
      ,[PersonSum]
      ,[TrainHistory]
      ,[Painful]
      ,[Pullulation]
      ,[TradeName]
      ,[SaleadviserName]
      ,[BargainReason]
      ,[BargainDispute]
      ,[BargainNum]  FROM  [V_CrmTeachSumup] where ThodometerId='" + id + "'";
            return BackDTbySQL(sql);
        }
        //
        public DataTable GetV_CrmCustomerOrder(string ID)
        {
            string sql = "select ID,Code FROM  [V_CrmCustomerOrder] where CustomerId='" + ID + "' and stateid in('af73673f-9fec-4d14-acfd-b0fce08d6ffa') and TypeID='5e50217a-414e-46d5-89bc-348ad0145ffc'";
            return BackDTbySQL(sql);
        }
        /// <summary>
        /// 根据客户ID获取讲师行程信息
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <returns></returns>
        public V_CrmTeachHodometer GetTeacherHodometerByCustomerId(Guid CustomerId)
        {
            this.entity = new V_CrmTeachHodometer();
            return GetEntityBySqlComm("select * from V_CrmTeachHodometer where CusID='" + CustomerId + "'") as V_CrmTeachHodometer;
        }

        public DataTable GetColNameByEntity(string eid)
        {
            string sql = "SELECT  [THCaption] ,[Field] FROM [Set_DataListTemplate] where EntityName='" + eid + "' order by iOrder";
            return BackDTbySQL(sql);
        }

        public DataTable GetColEntitys(string uid,string eid,DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0, max = dt.Rows.Count; i < max; i++)
            {
                sb.Append(dt.Rows[i]["Field"].ToString() + ",");
            }
            string sql = "SELECT " + sb.ToString().Substring(0, (sb.ToString().Length - 1)) + " FROM " + eid + " where AddUser='" + uid+"'";
            return BackDTbySQL(sql);
        }

        public DataTable GetV_CrmPackDetailByID(string packid)
        {
            string sql = @"SELECT ProductId,Code,TypeId,PhraseText,Caption,Money,
                        Amount, Rebate, RebateMoney, AccountReceivable, UnitId
                        FROM    [V_CrmPackDetail]
                        WHERE   (PackID = '" + packid+"')";
            return BackDTbySQL(sql);

        }

        public bool DeleteCrmPackDetail(string packid)
        {
            string sql = "delete FROM CrmPackDetail WHERE (PackID = '"+packid+"')";
            DbSql db = new DbSql();
            return db.ExecuteSql(sql)>1;
        }
    }
}
